using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;

namespace Company.BLL.KDT.SXXK
{
	public partial class BKToKhaiXuat 
	{
        public DataSet getBKToKhaiXuatNPL(long BangKeHoSoThanhLy_ID)
        {
            try
            {
                string sql = "SELECT a.SoToKhai,a.MaLoaiHinh,a.NamDangKy,a.NgayDangKy,a.MaHaiQuan,b.MaPhu,b.MaHS,b.TenHang, b.SoLuong, b.DVT_ID " +
                            "FROM t_KDT_SXXK_BKToKhaiXuat a INNER JOIN t_SXXK_HangMauDich b " +
                            "ON a.SoToKhai = b.SoToKhai AND a.MaLoaiHinh = b.MaLoaiHinh " +
                            "AND a.NamDangKy = b.NamDangKy AND a.MaHaiQuan = b.MaHaiQuan " +
                            "INNER JOIN t_SXXK_ToKhaiMauDich c " +
                            "ON a.SoToKhai = c.SoToKhai AND a.MaLoaiHinh = c.MaLoaiHinh " +
                            "AND a.NamDangKy = c.NamDangKy AND a.MaHaiQuan = c.MaHaiQuan " +
                            "WHERE BangKeHoSoThanhLy_ID =" + BangKeHoSoThanhLy_ID +
                            " AND c.Xuat_NPL_SP = 'N'";
                SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                return db.ExecuteDataSet(cmd);
            }
            catch
            {
                return null;
            }

        }
        public DataSet getBKToKhaiXuatSP(long BangKeHoSoThanhLy_ID)
        {
            try
            {
                string sql = "SELECT a.SoToKhai,a.MaLoaiHinh,a.NamDangKy,a.NgayDangKy,a.MaHaiQuan,b.MaPhu,b.MaHS,b.TenHang, b.SoLuong, b.DVT_ID " +
                            "FROM t_KDT_SXXK_BKToKhaiXuat a INNER JOIN t_SXXK_HangMauDich b " +
                            "ON a.SoToKhai = b.SoToKhai AND a.MaLoaiHinh = b.MaLoaiHinh " +
                            "AND a.NamDangKy = b.NamDangKy AND a.MaHaiQuan = b.MaHaiQuan " +
                            "INNER JOIN t_SXXK_ToKhaiMauDich c " +
                            "ON a.SoToKhai = c.SoToKhai AND a.MaLoaiHinh = c.MaLoaiHinh " +
                            "AND a.NamDangKy = c.NamDangKy AND a.MaHaiQuan = c.MaHaiQuan " +
                            "WHERE BangKeHoSoThanhLy_ID =" + BangKeHoSoThanhLy_ID +
                            " AND c.Xuat_NPL_SP = 'S'";
                SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                return db.ExecuteDataSet(cmd);
            }
            catch
            {
                return null;
            }

        }
        
        public DataSet getBKToKhaiXuatGC(long BangKeHoSoThanhLy_ID)
        {
            try
            {
                string sql = "SELECT a.SoToKhai,a.MaLoaiHinh,a.NamDangKy,a.NgayDangKy,a.MaHaiQuan " +
                            "FROM t_KDT_SXXK_BKToKhaiXuat a "+
                            "WHERE BangKeHoSoThanhLy_ID =" + BangKeHoSoThanhLy_ID +
                            " AND a.MaLoaiHinh LIKE 'XGC%'";
                SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                return db.ExecuteDataSet(cmd);
            }
            catch
            {
                return null;
            }

        }
        public DataSet getBKToKhaiXuatForReport(long bangKeHoSoThanhLy_ID)
        {
            try
            {
                string sql = "SELECT row_number() OVER (ORDER BY a.NgayDangKy) as STT, Cast(a.SoToKhai as varchar(100)) +'/' + substring(a.MaLoaiHinh,0,4) as SoToKhai, a.NgayDangKy, a.NgayThucXuat, b.SoHopDong, b.NgayHopDong, c.Ten  FROM t_KDT_SXXK_BKToKhaiXuat a inner join " +
                            "t_SXXK_ToKhaiMauDich b on a.SoToKhai = b.SoToKhai " +
                            "AND a.MaLoaiHinh = b.MaLoaiHinh AND a.MaHaiQuan = b.MaHaiQuan " +
                            "AND a.NamDangKy = b.NamDangKy inner join dbo.t_HaiQuan_DonViHaiQuan c " +
                            "ON b.MaHaiQuan = c.ID " +
                            "WHERE a.BangKeHoSoThanhLy_ID = " + bangKeHoSoThanhLy_ID;
                SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                return db.ExecuteDataSet(cmd);
            }
            catch
            {
                return null;
            }

        }
	}	
}